﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Build.Utilities;
using Microsoft.Build.Framework;
using System.Data.SqlClient;
using System.IO;
using UDS.Build.Database.Replication;
using UDS.Management.Data;
using UDS.Management.Data.Package;

namespace UDS.Build.Database
{
    public class GeneratePublicationSproc : DatabaseBuildTaskBase
    {
        private List<Publication> publications;

        [Required]
        public string PublicationSprocDir
        {
            get;
            set;
        }

        [Required]
        public ITaskItem[] PublicationDefinition
        {
            get;
            set;
        }

        private void LoadPublications()
        {
            ReplXMLHelper replLoader = new ReplXMLHelper();
            foreach (ITaskItem pubItem in PublicationDefinition)
            {
                Stream stm = File.OpenRead(pubItem.ItemSpec);
                using (ScriptSource src = new ScriptSource(pubItem.ItemSpec, stm))
                {
                    publications.Add(replLoader.Load(src));
                }
            }
            publications.Sort(
                // anonymous delegate
                delegate(Publication x, Publication y)
                {
                    return x.Version.CompareTo(y.Version);
                }
            );
        }

        private string[] GetDBColumns(SqlConnection conn, string tableName)
        {
            List<string> columns = new List<string>();
            SqlCommand cmd = new SqlCommand(string.Format("SELECT NAME FROM sys.columns WHERE object_id = OBJECT_ID('{0}', 'U') order by column_id asc", tableName), conn);
            using (SqlDataReader r = cmd.ExecuteReader())
            {
                while (r.Read())
                {
                    columns.Add(r.GetString(0));
                }
            }
            return columns.ToArray();
        }

        private bool CheckVerticalPartition(string[] DBColumns, PublicationPublishedTableArticleColumn[] publishedColumns)
        {
            // check if all columns are published
            if (DBColumns.Length == publishedColumns.Length)
            {
                // if all columns are published, check they are in the same order
                for (int i = 0; i < DBColumns.Length; i++)
                {
                    if (DBColumns[i] != publishedColumns[i].ColumnName)
                    {
                        return true;
                    }
                }
                return false;
            }
            return true;
        }

        public override bool Execute()
        {
            publications = new List<Publication>();
            LoadPublications();
            bool hasErrors = false;
            //SqlDataReader reader = null;
            EmbeddedSqlScriptReader scriptReader = new EmbeddedSqlScriptReader();
            Log.LogMessage(MessageImportance.High, "Generating Publication sproc on database - {0} {1}", SqlServerInstance, DatabaseName);
            using (SqlConnection conn = MakeConnection())
            {
                PublicationDefinition pubDef = new PublicationDefinition();

                foreach (Publication pub in publications)
                {
                    pubDef.Load(pub);
                    string fileNameSuffix;
                    string procNameSuffix = string.Format("_V_{0}", pub.Version.Replace(".", "_"));;
                    if (pub.Version == "1.1")
                    {
                        fileNameSuffix = string.Empty;
                    }
                    else
                    {
                        fileNameSuffix = procNameSuffix;
                    }
                    string procName = string.Format("sproc_db_CreatePublicationBranch{0}", procNameSuffix);
                    Log.LogMessage(MessageImportance.High, "Creating publication sproc {0} for publication version {1}...", procName, pub.Version);
                    StringWriter w = new StringWriter();
                    SqlWriter sqlW = new SqlWriter(w);
                    sqlW.WriteSprocProlog("dbo", procName);

                    string pubName = pub.Name + procNameSuffix;
                    foreach (PublishedTableDefinition table in pubDef.Tables)
                    {
                        if (table.Columns != null && table.Columns.Length > 0)
                        {
                            string verticalPartition = "false";
                            string viewName = null;
                            // Check if vertical partition
                            if (CheckVerticalPartition(GetDBColumns(conn, table.Name), table.Columns))
                            {
                                viewName = "SYNC_BranchDB_" + table.Name + procNameSuffix;
                                sqlW.WriteViewDrop("dbo", viewName, false);
                                verticalPartition = "true";
                            }

                            sqlW.WriteText("exec sp_addarticle @publication = '");
                            sqlW.WriteText(pubName);
                            sqlW.WriteText("', @article = N'");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @source_owner = N'dbo', @source_object = N'");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @destination_table = N'synctbl_");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = ");
                            sqlW.WriteText(table.SchemaOption);
                            sqlW.WriteText(" , @status = 16, @vertical_partition = N'");
                            sqlW.WriteText(verticalPartition);
                            sqlW.WriteText("', @ins_cmd = N'CALL sp_MSins_");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @del_cmd = N'CALL sp_MSdel_");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @upd_cmd = N'MCALL sp_MSupd_");
                            sqlW.WriteText(table.Name);
                            sqlW.WriteText("', @filter = null, @sync_object = null, @identityrangemanagementoption = N'manual'\r\n\r\n");

                            if (verticalPartition == "true")
                            {
                                foreach (PublicationPublishedTableArticleColumn col in table.Columns)
                                {
                                    sqlW.WriteText("exec sp_articlecolumn @publication = '");
                                    sqlW.WriteText(pubName);
                                    sqlW.WriteText("', @article = N'");
                                    sqlW.WriteText(table.Name);
                                    sqlW.WriteText("', @column = N'");
                                    sqlW.WriteText(col.ColumnName);
                                    sqlW.WriteText("', @operation = N'add'\r\n");
                                }
                                sqlW.WriteText("exec sp_articleview @publication = '");
                                sqlW.WriteText(pubName);
                                sqlW.WriteText("', @article = N'");
                                sqlW.WriteText(table.Name);
                                sqlW.WriteText("', @view_name = N'");
                                sqlW.WriteText(viewName);
                                sqlW.WriteText("'\r\n\r\n");
                            }

                        }
                    }
                    sqlW.WriteSprocEpilog();

                    string scriptName = Path.Combine(PublicationSprocDir, string.Format("sproc_db_CreatePublicationBranch{0}.sql", fileNameSuffix));
                    try
                    {
                        string pubSql = w.ToString();
                        if (File.Exists(scriptName))
                        {
                            string scriptText;
                            using (StreamReader r = new StreamReader(File.OpenRead(scriptName)))
                            {
                                scriptText = r.ReadToEnd();
                            }
                            if (string.Compare(pubSql, scriptText, true) != 0)
                            {
                                try
                                {
                                    File.WriteAllText(scriptName, pubSql);
                                }
                                catch (UnauthorizedAccessException ex)
                                {
                                    hasErrors = true;
                                    Log.LogError("You need to edit the Publication stored procedures for the publication [{0}] for the file [{1}] before check-in. Execption {2}.", pub.Version, scriptName, ex.Message);
                                }
                            }
                        }
                        else
                        {
                            hasErrors = true;
                            File.WriteAllText(scriptName, pubSql);
                            Log.LogError("You need to add a new Replication stored procedures file named [{1}] for the publication [{0}] before check-in.", pub.Version, scriptName);
                        }
                    }
                    catch (Exception e)
                    {
                        hasErrors = true;
                        Log.LogErrorFromException(e, true, true, scriptName);
                    }
                }
            }
            return !hasErrors;
        }
    }
}
